package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.BookDao;
import bookshop.dao.CustomerDao;
import bookshop.dao.DaoFactory;
import bookshop.dao.OrderDao;
import bookshop.dao.ReceiveAddrDao;
import bookshop.domain.OrderInfo;
import bookshop.domain.OrderItem;
import bookshop.domain.SaleBook;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.OrderQueryHelper;
import bookshop.utils.DBUtils;

public class OrderDaoJDBCImpl implements OrderDao {

	private static final String ADD_ORDER_ITEM = "insert into order_item(item_bookcode,item_amount,item_bookprice,order_id) values(?,?,?,?)";
	private static final String ADD_ORDER_INFO = "insert into order_info(order_id,order_time,rece_id,pay_mode,delivery_mode,order_amount,custs_id,order_status) values(?,?,?,?,?,?,?,?)";
	private static final String GET_ORDERITEM_BYORDERID = "select * from order_item where order_id=?";
	private static final String DEL_ORDER = "delete from order_info where order_id=?";
	private static final String DEL_ORDER_ITEM = "delete from order_item where order_id=?";
	private static final String UPDATE_ORDER = "update order_info set order_time=?,rece_id=?,pay_mode=?,delivery_mode=?,order_amount=?,custs_id=?,order_status=? where order_id=?";
	private static final String GetOrderByOrderId = "select * from order_info where order_id=?";
    private static final String GETSALESBOOK="select * from order_item where order_id in (select order_id from order_info where order_status='D')";
    private static final String GETSALEBOOK="select * from order_item where order_id in (select order_id from order_info where order_status='D') group by item_bookcode";
	private String generateSQL(OrderQueryHelper helper) {

		String sql = "select * from order_info where 1=1 ";

		if(helper.getQryOrderId()!=null)
			sql +=" and order_id like '%"+helper.getQryOrderId()+"%' ";
		
		if(helper.getQryOrderStatus()!=null)
			sql +=" and order_status = '"+helper.getQryOrderStatus()+"' ";
		
		if(helper.getQryCustId()!=null)
			sql +=" and custs_id = '"+helper.getQryCustId()+"' ";
		
		if(helper.getQryOrderYear()!=null){
			sql += " and date_format(order_time,'%Y') = '" + helper.getQryOrderYear()+"'";
			
		}
		
		
		if(helper.getQryOrderMonth()!=null){
			sql += " and date_format(order_time,'%m') = '" + helper.getQryOrderMonth()+"'";
			
		}
		
		if(helper.getQryOrderDay()!=null){
			sql += " and date_format(order_time,'%d') = '" + helper.getQryOrderDay()+"'";
			
		}
		
		sql += " order by order_id";

		return sql;

	}

	public void AddOrderInfo(OrderInfo orderInfo) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {

			ps = ct.prepareStatement(ADD_ORDER_INFO);

			ps.setString(1, orderInfo.getOrderId());
			ps.setTimestamp(2, new java.sql.Timestamp(orderInfo.getOrderTime()
					.getTime()));
			ps.setInt(3, orderInfo.getReceiveAddr().getReceiverId());
			ps.setString(4, orderInfo.getPayMode());
			ps.setString(5, orderInfo.getDeliveryMode());
			ps.setDouble(6, orderInfo.getOrderAmount());
			ps.setString(7, orderInfo.getCustomer().getCustomerId());
			ps.setString(8, orderInfo.getOrderStatus());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("订单添加失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
	}

	public void saveOrderItem(OrderItem orderItem) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {
			ps = ct.prepareStatement(ADD_ORDER_ITEM);
			ps.setString(1, orderItem.getBook().getBookCode());
			ps.setInt(2, orderItem.getItemBookAmount());
			ps.setDouble(3, orderItem.getItemBookPrice());
			ps.setString(4, orderItem.getOrderId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("购买图书信息保存失败，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
	}

	public List<OrderInfo> GetOrderInfoByCondition(OrderQueryHelper helper) {
		String sql = this.generateSQL(helper);

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		OrderInfo order = null;
		List<OrderInfo> orders = new ArrayList<OrderInfo>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			ReceiveAddrDao receiveAddrDao = (ReceiveAddrDao) DaoFactory
					.getDao("receiveAddrDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory
					.getDao("customerDao");
			while (rs.next()) {
				order = new OrderInfo();

				order.setOrderId(rs.getString(1));
				order.setOrderTime(new java.util.Date(rs.getTimestamp(2)
						.getTime()));
				order.setReceiveAddr(receiveAddrDao.GetReceiverAddrById(rs
						.getInt(3)));
				order.setPayMode(rs.getString(4));
				order.setDeliveryMode(rs.getString(5));
				order.setOrderAmount(rs.getDouble(6));
				order.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				order.setOrderStatus(rs.getString(8));

				orders.add(order);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return orders;

	}

	public Long countOrdersByCondition(OrderQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) as total");
		sql = sql.substring(0, sql.indexOf("order by"));

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return count;
	}

	public List<OrderInfo> getScopedOrders(OrderQueryHelper helper,
			int startIndex, int fetchSize) {
		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;

		System.out.println(sql);
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		OrderInfo order = null;
		List<OrderInfo> orders = new ArrayList<OrderInfo>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			ReceiveAddrDao receiveAddrDao = (ReceiveAddrDao) DaoFactory
					.getDao("receiveAddrDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory
					.getDao("customerDao");
			while (rs.next()) {
				order = new OrderInfo();

				order.setOrderId(rs.getString(1));
				order.setOrderTime(new java.util.Date(rs.getTimestamp(2)
						.getTime()));
				order.setReceiveAddr(receiveAddrDao.GetReceiverAddrById(rs
						.getInt(3)));
				order.setPayMode(rs.getString(4));
				order.setDeliveryMode(rs.getString(5));
				order.setOrderAmount(rs.getDouble(6));
				order.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				order.setOrderStatus(rs.getString(8));

				orders.add(order);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return orders;
	}

	public List<OrderItem> GetOrderItemByOrderId(String orderId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		OrderItem orderItem = null;
		List<OrderItem> orderItems = new ArrayList<OrderItem>();

		try {
			ps = ct.prepareStatement(GET_ORDERITEM_BYORDERID);
			ps.setString(1, orderId);
			rs = ps.executeQuery();
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			while (rs.next()) {
				orderItem = new OrderItem();

				orderItem.setItemId(rs.getInt(1));

				orderItem.setBook(bookDao.getBookByCode(rs.getString(2)));
				orderItem.setItemBookAmount(rs.getInt(3));
				orderItem.setItemBookPrice(rs.getDouble(4));
				orderItem.setOrderId(rs.getString(5));

				orderItems.add(orderItem);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return orderItems;

	}

	public void DelOrder(String orderId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(DEL_ORDER);
			ps.setString(1, orderId);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	public void DelOrderItem(String orderId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(DEL_ORDER_ITEM);
			ps.setString(1, orderId);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	public void UpdateOrder(OrderInfo orderInfo) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {

			ps = ct.prepareStatement(UPDATE_ORDER);

			ps.setTimestamp(1, new java.sql.Timestamp(orderInfo.getOrderTime()
					.getTime()));
			ps.setInt(2, orderInfo.getReceiveAddr().getReceiverId());
			ps.setString(3, orderInfo.getPayMode());
			ps.setString(4, orderInfo.getDeliveryMode());
			ps.setDouble(5, orderInfo.getOrderAmount());
			ps.setString(6, orderInfo.getCustomer().getCustomerId());
			ps.setString(7, orderInfo.getOrderStatus());
			ps.setString(8, orderInfo.getOrderId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("订单更新失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	public OrderInfo GetOrderByOrderId(String orderId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		OrderInfo order = null;

		try {
			ps = ct.prepareStatement(GetOrderByOrderId);
			ps.setString(1, orderId);
			rs = ps.executeQuery();
			ReceiveAddrDao receiveAddrDao = (ReceiveAddrDao) DaoFactory
					.getDao("receiveAddrDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory
					.getDao("customerDao");
			while (rs.next()) {
				order = new OrderInfo();

				order.setOrderId(rs.getString(1));
				order.setOrderTime(new java.util.Date(rs.getTimestamp(2)
						.getTime()));
				order.setReceiveAddr(receiveAddrDao.GetReceiverAddrById(rs
						.getInt(3)));
				order.setPayMode(rs.getString(4));
				order.setDeliveryMode(rs.getString(5));
				order.setOrderAmount(rs.getDouble(6));
				order.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				order.setOrderStatus(rs.getString(8));

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return order;
	}

    public List<OrderItem> GetSalesBook(){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		OrderItem orderItem = null;
		List<OrderItem> orderItems = new ArrayList<OrderItem>();
		try {
			ps = ct.prepareStatement(GETSALESBOOK);
			rs = ps.executeQuery();
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			
			while (rs.next()) {
				orderItem = new OrderItem();

				orderItem.setItemId(rs.getInt(1));

				orderItem.setBook(bookDao.getBookByCode(rs.getString(2)));
				orderItem.setItemBookAmount(rs.getInt(3));
				orderItem.setItemBookPrice(rs.getDouble(4));
				orderItem.setOrderId(rs.getString(5));

				orderItems.add(orderItem);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("订单更新失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		return orderItems;
    }
    
    public List<SaleBook> GetSaleBook(){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		OrderItem orderItem = null;
		List<SaleBook> sales = new ArrayList<SaleBook>();
		try {
			ps = ct.prepareStatement(GETSALEBOOK);
			rs = ps.executeQuery();
			SaleBook sale = null;
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			while (rs.next()) {
				sale = new SaleBook();	
				sale.setBook(bookDao.getBookByCode(rs.getString(2)));
				sale.setNumber(0);
				sales.add(sale);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("订单更新失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		return sales;
    }
}
